An Example, Replace, & Substitute

Let’s look at an example of an address in which you might use some of these functions. 

As in the example used in the training tape, you may want to separate the zip from the rest of an address and into a separate column.  To quickly do this, you can use the right function.  Simply place the cursor in the cell where you want the code to appear, and use =right(Cell address,5).  The 5 is used because the zip codes in this example are five digit codes.  When you hit Enter, the zip code will be automatically written in the designated cell.

Using the same address, you can pull out the state name by using the mid  and find functions.  To pull out the state, use the formula =mid(cell address,find(“,”,cell address,1)+2,2).  This information will pick out the middle 2 characters, starting at a certain specified location within the cell.

Now lets look at a few more new functions.  Return to the Funexam worksheet from your data files.

Notice on this sheet the functions in columns F and G.  These functions are the replace and substitute functions.  In this example, we will use the replace function to replace a certain character with another character.

Skill Builder:  Using the Replace Function 

1.	Click 	In the Cell where you want the new value to appear.

2.	Type	The Function.  In this case the function is 

		=replace(cell address of cell in which you wish to make 
		a replacement, the position at which you wish to begin 
		replacing characters, the number of characters you wish 
		to replace, what you wish to replace the character with)

		For example, if you wanted to replace the only the 5th 
		character of cell B17 with an X, you would use the 
		formula:  
		=replace(B17,5,1,”X”)

3.  	Press	Enter.  When you press enter the specified character(s) 
		will be replaced and your new value will appear.

The substitute function works in a similar manner to the replace function.  However, with the substitute function, rather than specifying the number of the character you would like to replace, you specify the character itself.

Skill Builder:  Using the Substitute Function 

1.	Click 	In the Cell where you want the new value to appear.

2.	Type	The Function.  In this case the function is 
		
		=substitute(cell address of cell in which you wish to 
		make a replacement, the character which you wish to 
		replace, the character you wish to replace with)

		For example, if you wanted to replace all of the letter 
		G’s in cell E17 with Z’s, you would use the formula:
		=substitute(E17,”G”,”Z”)
 
3.  	Press	Enter.  When you press enter the specified character 
		will be replaced and your new value will appear.